local license =
[[----------------------------------------------------------------------
  **** BEGIN LICENSE BLOCK ****
	A simple flocking simulator written in LUA.

    Copyright (C) 2010
	Eric Fredericksen
	www.pttpsystems.com
	eric@pttpsystems.com

	This file is part of Flocker.

    Flocker is free software: you can redistribute it and/or modify
    it under the terms of the GNU Affero General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Affero General Public License for more details.

    You should have received a copy of the GNU Affero General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.

  **** END LICENSE BLOCK ****
------------------------------------------------------------------------]]


require( "flockerutils"		)
require( "luasql.sqlite3"	)
require( "iuplua"			)

require( "base64"			)

-- global scope object for our code; let's keep things tidy
assert (flocker, "Flocker DB: Failed to find global variable flocker")

-- namespace for database functions
flocker.db = {}

--[[ ====================== dataabase access stuff below ========================= ]]

function flocker.db.GrabUIConfig()

	-- create environment object
	local environment = assert (luasql.sqlite3(), "grabConfig: Failed to initialize sqlite")

	-- connect to data source
	local connection = assert (environment:connect("flocker.sqlite"), "grabConfig: Failed to connect to database.")

	-- retrieve a cursor
	local configCursor = assert( connection:execute"SELECT * FROM configuration" )
	local config = configCursor:fetch( {}, "a" )
	configCursor:close()

	connection:close()
	environment:close()

	return config
end


function flocker.db.SaveUIConfig( size, posx, posy )

		-- create environment object
	local environment = assert (
		luasql.sqlite3()
		, "flockUI:close_cb: Failed to initialize sqlite"
		)

	-- connect to data source
	local connection = assert (
		environment:connect("flocker.sqlite")
		, "flockUI:close_cb: Failed to connect to database."
		)

	-- we need to update the visible configuration for the application
	local dataUpdateStmt = 	string.format(
		[[	INSERT OR REPLACE INTO configuration (id, size, posx, posy)
			VALUES ( 1, '%s', %d, %d )
			]]
		, size, posx, posy
		)

	-- retrieve a cursor
	local result = assert( connection:execute(dataUpdateStmt) )

	connection:close()
	environment:close()

end



function flocker.db.SaveBlob( blobType, dialogTitle, blob)

	assert(blobType and "string"==type(blobType), "SaveBlob: bad blobType:" .. tostring(blobType) )
	assert(dialogTitle and "string"==type(dialogTitle), "SaveBlob: bad dialogTitle:" .. tostring(dialogTitle) )

	local timestamp		= os.date()
	local name 			= "Snapshot taken " .. timestamp
	local description 	= "Your description here."
	local format 		= dialogTitle .. "\nName:%300.40%s\nDescription:%300.40%s\n"

	name, description = iup.Scanf (format, name, description)

	-- check for error
	if( not name ) then return end

	-- proceed to save the data

	-- create environment object
	local environment	= assert( luasql.sqlite3(), "SaveBlob: Failed to initialize sqlite." )

	-- connect to data source
	local connection	= assert( environment:connect("flocker.sqlite"), "SaveBlob: Failed to connect to database.")

	-- create an insert statement for the snapshot summary
	local insertStmt = string.format(
		[[	INSERT INTO blobstore
			(timestamp, type, name, description, blob)
			VALUES ('%s', '%s', '%s', '%s', '%s' )
			]]
		, timestamp, blobType, name, description, base64.encode(blob)
		)

	assert( connection:execute( insertStmt ), "Blob store insertion failed: ".. insertStmt )

	-- clean up when we are done
	connection:close()
	environment:close()

end


function flocker.db.SelectBlobs( dbConnection, multiselect, blobType, dialogTitle )

	assert(blobType and "string"==type(blobType), "SelectBlobs: bad blobType:" .. tostring(blobType) )
	assert(dialogTitle and "string"==type(dialogTitle), "SelectBlobs: bad dialogTitle:" .. tostring(dialogTitle) )

	-- execute select and retrieve a cursor -- order by most recent first
	local selectStmt = string.format(
		[[	SELECT * FROM blobstore
			WHERE type = '%s'
			ORDER BY timestamp DESC ]]
		, blobType
		)
	local blobCursor = assert( dbConnection:execute( selectStmt ) )

	-- accumulate all rows into a table for display

	local itemTable		= {}
	local blobIDTable	= {}
	local selectedList	= {}

	-- the rows will be indexed by field names; returns the table we passed in
	local thisBlob = blobCursor:fetch( {}, "a" )

	if not thisBlob then
		-- close this now
		blobCursor:close()
		iup.Message(dialogTitle, "There are no entries in the database.\nTry again some other time. :)")
		return -1, blobIDTable, selectedList
	end

	while thisBlob do

	  local listItem = string.format( "%s - '%s' - '%s'"
				, thisBlob.timestamp 	or "No timestamp"
				, thisBlob.name 		or "No name"
				, thisBlob.description 	or "No description."
				)

	  --[[ 	We can choose to store the whole data set, or to
			re-retrieve the selected row. The latter is a better
			memory profile if the table grew to large sizes
			(say, 10^6 or more rows), so let's take the trouble.
		]]
	  table.insert( itemTable, 			listItem		)
	  table.insert( blobIDTable,	thisBlob.id	)
	  table.insert( selectedList,		0				)

	-- fetch another row, reusing the same table for data
	  thisBlob = blobCursor:fetch( thisBlob, "a" )
	end

	-- close this now
	blobCursor:close()

	dialogTitle = dialogTitle or string.format( "Pick A [%q] BLOB", blobType )
	local result = iup.ListDialog(
		  multiselect and 2 or 1	-- single or multiselect select
		, dialogTitle				-- dialog title
		, #itemTable 				-- option count
		, itemTable					-- table of strings to present
		, 1 						-- initial selected item
		, 50 						-- max cols
		, 10 						-- max lines
		, selectedList				-- marks for selected items
		)

	--[[ For some reason this dialog returns zero-based index instead
		of 1-based index, so we update it to match the tables we return
		]]
	if( result > -1 ) then result = result + 1 end
	return result, blobIDTable, selectedList

end


function flocker.db.LoadBlob( blobType, dialogTitle, blobNameString )

	-- create environment object
	local environment	= assert (luasql.sqlite3(), "LoadBlob: Failed to initialize sqlite")

	-- connect to data source
	local dbConnection	= assert (environment:connect("flocker.sqlite"), "LoadBlob: Failed to connect to database")

	-- for blobs, the interpretation must be performed by the caller; return the blob
	local selectedBlob = nil

	if( blobNameString ) then
			-- grab a new cursor for the one specific row
		local blobRetrieverStmt	= string.format(
			[[
			SELECT * FROM blobstore
			WHERE type = '%s' AND name LIKE '%s'
			ORDER BY id DESC
			]]
			,blobType, blobNameString
			)

		local blobCursor 		= assert( dbConnection:execute(blobRetrieverStmt) )
		-- we only return the first one, not a list (until this behavior needs to change)
		selectedBlob			= blobCursor:fetch({},"a")
		blobCursor:close()		-- clean up right away

	else -- ask the user what they want

		local result, blobIDTable = flocker.db.SelectBlobs( dbConnection, false, blobType, dialogTitle  )

		-- a value of -1 means cancel
		if -1 < result then

			-- do the bird loading in here
			local chosenBlobID = blobIDTable[ result ]

			-- grab a new cursor for the one specific row
			local blobRetrieverStmt	= string.format( "SELECT * FROM blobstore WHERE id = %d", chosenBlobID)
			local blobCursor 		= assert( dbConnection:execute(blobRetrieverStmt) )
			selectedBlob			= blobCursor:fetch({},"a")
			blobCursor:close()		-- clean up right away

		end

	end

	-- clean up when we are done
	dbConnection:close()
	environment:close()

	return selectedBlob and selectedBlob.blob and base64.decode(selectedBlob.blob)
end

function flocker.db.DeleteBlob( blobType, dialogTitle )

	assert(blobType and "string"==type(blobType), "DeleteBlob: bad blobType:" .. tostring(blobType) )

	-- create environment object
	local environment	= assert (luasql.sqlite3(), "DeleteBlob: Failed to initialize sqlite")

	-- connect to data source
	local dbConnection	= assert (environment:connect("flocker.sqlite"), "DeleteBlob: Failed to connect to database")


	local result, blobIDTable, selectedList = flocker.db.SelectBlobs( dbConnection, true, blobType, dialogTitle )

	-- a value of -1 means cancel
	if -1 < result then

		-- so or selection information is in the two tables
		for blobIDIndex, selectionMark in pairs( selectedList ) do

			if( 1 == selectionMark ) then

				local chosenBlobID = blobIDTable[ blobIDIndex ]

				-- grab a new cursor for the one specific row
				local blobDeleteStmt	= string.format( "DELETE FROM blobstore WHERE id = %d", chosenBlobID)
				local deleteResult	 	= assert( dbConnection:execute(blobDeleteStmt) )

				if( 1 ~= deleteResult ) then
					iup.Message( "Blob delete failed.", "The SQL query was:\n"..blobDeleteStmt )
				end
			end
		end
	end

	-- clean up when we are done
	dbConnection:close()
	environment:close()
end


function flocker.db.SaveSnapshot()
	--[[
		the date/time is only good to one second.
		It is unlikely that anyone can save more than once a second,
		and yet, it is a failure mode if it is used as a unique key.
		I'll use an autoincrement row number but search/sort by snapshot time and/or name.
	]]
	local timestamp		= os.date()
	local name 			= "Snapshot taken " .. timestamp
	local description 	= "Your description here."
	local format 		= "I need just a little information. :)\nName:%300.40%s\nDescription:%300.40%s\n"

	name, description = iup.Scanf (format, name, description)

	-- check for error
	if( not name ) then return end

	-- proceed to save the data

	-- create environment object
	local environment	= assert( luasql.sqlite3(), "SaveSnapshot: Failed to initialize sqlite." )

	-- connect to data source
	local connection	= assert( environment:connect("flocker.sqlite"), "SaveSnapshot: Failed to connect to database.")

	--[[
		do this as a transaction:
		insert new row into snapshot table
		insert new rows into objects table
		commit transaction
	]]

	local birdCount = flocker.birdCount
	local result = nil
	result = assert( connection:execute"BEGIN IMMEDIATE TRANSACTION" )

	-- create an insert statement for the snapshot summary
	local insertSnapshotStmt = string.format(
		[[	INSERT INTO snapshot
			(timestamp, name, description, birdcount, repel, detect,
			deltapx, deltat, caffeine, agility, antisociality,
			threedeeflocking, worldzdepth, reflectbirds, worldboxgap, fov,
			enablefog, fogintensity,
			partisan, aggressive, showrepel, showdetect, bgcolor)
			VALUES ('%s', '%s', '%s', %d, %f, %f,
			%f, %f, %f, %f, %f,
			%d, %f, %d, %d, %d,
			%d, %f,
			%d, %d, %d, %d, %d)
			]]
		, timestamp
		, name
		, description
		, birdCount
		, flocker.repulseR
		, flocker.detectR

		, flocker.deltaPX
		, flocker.deltaT
		, flocker.caffeine
		, flocker.agility
		, flocker.antisociality

		, flocker.threeDeeFlocking	and 1 or 0
		, flocker.toroidZ
		, flocker.reflectBirds 		and 1 or 0
		, flocker.ogl.worldBoxGap
		, flocker.ogl.camera.fov

		, flocker.ogl.enableFog 	and 1 or 0
		, flocker.ogl.fogIntensity

		, flocker.partisan 		and 1 or 0
		, flocker.aggressive 	and 1 or 0
		, flocker.showRepel 	and 1 or 0
		, flocker.showDetect 	and 1 or 0
		, flocker.bgColor
		)

	--print(insertSnapshotStmt)
	result = assert( connection:execute( insertSnapshotStmt ) )

	-- if this is not '1' then we failed for some reason
	if( 1 ~= result ) then
		iup.Message( "Snapshot insertion failed."
			, "Rolling back the transaction from:\n"..insertSnapshotStmt
			)
		assert( connection:execute("ROLLBACK TRANSACTION") )
		return
	end

	-- get the latest indexed created for the new snapshot insertion
	local snapshotID = -1
	do
		local snapshotIDcursor = assert( connection:execute"SELECT max(id) AS maxid FROM snapshot")
		local row = snapshotIDcursor:fetch( {}, "a" )
		snapshotID = row.maxid
		snapshotIDcursor:close()
	end

	-- insert each bird into the objects table with the snapshot id as key
	-- this should be fast because we are inside a BEGIN...COMMIT block
	for __, bird in pairs(flocker.birds) do

		local insertBirdStmt = string.format(
			[[	INSERT INTO objects
				(snapshotid, type, posx, posy, posz, dirx, diry, dirz, health, age)
				VALUES (%d, %d, %f, %f, %f, %f, %f, %f, %d, %d)
				]]
			, snapshotID
			, bird.typeId
			, bird.p[1], bird.p[2], bird.p[3]
			, bird.d[1], bird.d[2], bird.d[3]
			, math.floor(bird.h)
			, bird.a
			)

		--print(insertBirdStmt)
		result = assert( connection:execute( insertBirdStmt ) )

		if( 1 ~= result ) then
			iup.Message( "Object insertion failed."
				, "Rolling back the transaction from:\n"..insertBirdStmt
				)
			assert( connection:execute("ROLLBACK TRANSACTION") )
			return
		end

	end

	result = assert( connection:execute"COMMIT TRANSACTION" )

	-- clean up when we are done
	connection:close()
	environment:close()


end


--  let user select a snapshot using a ListDialog
function flocker.db.SelectSnapshots( dbConnection, multiselect )

	-- execute select and retrieve a cursor -- order by most recent first
	local snapshotCursor = assert( dbConnection:execute"SELECT * FROM snapshot ORDER BY timestamp DESC" )

	-- accumulate all rows into a table for display

	local itemTable			= {}
	local snapshotIDTable	= {}
	local selectedList		= {}

	-- the rows will be indexed by field names; returns the table we passed in
	local thisSnapshot = snapshotCursor:fetch( {}, "a" )

	if not thisSnapshot then
		-- close this now
		snapshotCursor:close()
		iup.Message("Bird configuration snapshots.", "There are no entries in the database.\nTry again some other time. :)")
		return -1, snapshotIDTable, selectedList
	end


	while thisSnapshot do

	  local listItem = string.format( "%s - '%s' - %d birds - '%s'"
				, thisSnapshot.timestamp 	or "No timestamp"
				, thisSnapshot.name 		or "No name"
				, thisSnapshot.birdcount 	or -1
				, thisSnapshot.description 	or "No description."
				)

	  --[[ 	We can choose to store the whole data set, or to
			re-retrieve the selected row. The latter is a better
			memory profile if the table grew to large sizes
			(say, 10^6 or more rows), so let's take the trouble.
		]]
	  table.insert( itemTable, 			listItem		)
	  table.insert( snapshotIDTable,	thisSnapshot.id	)
	  table.insert( selectedList,		0				)

	-- fetch another row, reusing the same table for data
	  thisSnapshot = snapshotCursor:fetch( thisSnapshot, "a" )
	end

	-- close this now
	snapshotCursor:close()

	local result = iup.ListDialog(
		  multiselect and 2 or 1	-- single or multiselect select
		, "Pick A Snapshot"			-- dialog title
		, #itemTable 				-- option count
		, itemTable					-- table of strings to present
		, 1 						-- initial selected item
		, 50 						-- max cols
		, 10 						-- max lines
		, selectedList				-- marks for selected items
		)

	--[[ For some reason this dialog returns zero-based index instead
		of 1-based index, so we update it to match the tables we return
		]]
	if( result > -1 ) then result = result + 1 end
	return result, snapshotIDTable, selectedList
end


function flocker.db.VacuumDatabase()
	-- create environment object
	local environment	= assert (luasql.sqlite3(), "VacuumDatabase: Failed to initialize sqlite")

	-- connect to data source
	local dbConnection	= assert (environment:connect("flocker.sqlite"), "VacuumDatabase: Failed to connect to database")

	local vacuumStmt = "VACUUM"
	local result = assert( dbConnection:execute(vacuumStmt) )

	if( 1 ~= result ) then
		iup.Message( "Database size reduction failed.", "The SQL query was:\n"..vacuumStmt )
	end

		-- clean up when we are done
	dbConnection:close()
	environment:close()

end


-- delete a snapshot
function flocker.db.DeleteSnapshot()

	-- create environment object
	local environment	= assert (luasql.sqlite3(), "DeleteSnapshot: Failed to initialize sqlite")

	-- connect to data source
	local dbConnection	= assert (environment:connect("flocker.sqlite"), "DeleteSnapshot: Failed to connect to database")

	do
		--[[	Database trigger check and maintenance
			If you modify a table in SQLite you can only do so by recreating a table.
			Sadly, this also drops any associated triggers. I like using the trigger
			becaus it automates the transaction nature of the delete.
			In other words, less work for me. :)
		]]
		local triggerSearchCursor = dbConnection:execute(
			[[	SELECT * FROM sqlite_master
				WHERE   type		= 'trigger'
					AND tbl_name	= 'snapshot'
					AND name		= 'cascade_delete_objects'
			]]
			)
		assert( triggerSearchCursor ) -- check for *really* bad error

		-- try to grab the first row
		local requiredTrigger = triggerSearchCursor:fetch( {}, "a" )

		if not requiredTrigger then
			print("(re)inserting object delete trigger. Someone mucked with the snapshot table.")

			assert(dbConnection:execute(
			[[	CREATE TRIGGER "cascade_delete_objects"
				BEFORE DELETE ON snapshot FOR EACH ROW
				BEGIN DELETE FROM objects WHERE snapshotid = OLD.id; END]]
				))
		end
		-- close this now
		triggerSearchCursor:close()
	end

	local result, snapshotIDTable, selectedList = flocker.db.SelectSnapshots( dbConnection, true )

	-- a value of -1 means cancel
	if -1 < result then

		-- so or selection information is in the two tables
		for snapshotIDIndex, selectionMark in pairs( selectedList ) do

			if( 1 == selectionMark ) then

				local chosenSnapshotID = snapshotIDTable[ snapshotIDIndex ]

				-- grab a new cursor for the one specific row
				local snapshotDeleteStmt	= string.format( "DELETE FROM snapshot WHERE id = %d", chosenSnapshotID)
				local deleteResult	 		= assert( dbConnection:execute(snapshotDeleteStmt) )

				if( 1 ~= deleteResult ) then
					iup.Message( "Snapshot delete failed.", "The SQL query was:\n"..snapshotDeleteStmt )
				end
			end
		end
	end

	-- clean up when we are done
	dbConnection:close()
	environment:close()

end


function flocker.db.LoadDataForSnapshot( dbConnection, snapshot )
	-- we assume we ahve the dbConnection already

		-- extract configuration and restore, making sure the UI updates
		flocker.repulseR		= tonumber(snapshot.repel)
		flocker.detectR			= tonumber(snapshot.detect)
		flocker.deltaPX			= tonumber(snapshot.deltapx)
		flocker.deltaT			= tonumber(snapshot.deltat)
		flocker.caffeine		= tonumber(snapshot.caffeine)
		flocker.agility			= tonumber(snapshot.agility)
		flocker.antisociality	= tonumber(snapshot.antisociality)

		flocker.partisan	 	= 1==tonumber(snapshot.partisan)
		flocker.aggressive	 	= 1==tonumber(snapshot.aggressive)
		flocker.showRepel 		= 1==tonumber(snapshot.showrepel)
		flocker.showDetect 		= 1==tonumber(snapshot.showdetect)

		flocker.threeDeeFlocking= 1==tonumber(snapshot.threedeeflocking)
		flocker.toroidZ			= tonumber(snapshot.worldzdepth)

		flocker.reflectBirds	= 1==tonumber(snapshot.reflectbirds)

		flocker.ogl.worldBoxGap	= tonumber(snapshot.worldboxgap)
		flocker.ogl.camera.fov	= tonumber(snapshot.fov)

		flocker.ogl.enableFog 	= 1==tonumber(snapshot.enablefog)
		flocker.ogl.fogIntensity= tonumber(snapshot.fogintensity)

		flocker.bgColor			= tonumber(snapshot.bgcolor or 1)

		-- remove previous birds
		flocker.birds = {}
		flocker.birdCount = 0

		-- start adding back in the restored birds
		local chosenSnapshotID = snapshot.id
		local birdCollectorStmt = string.format("SELECT * FROM objects WHERE snapshotid=%d", chosenSnapshotID)

		local birdCursor = 	assert( dbConnection:execute(birdCollectorStmt) )

		local aBirdToLoad = birdCursor:fetch({}, "a")
		while aBirdToLoad do
			flocker.createNewBird(
				  flocker.birdTypes[ tonumber(aBirdToLoad.type) ]
				, vec3.new( tonumber(aBirdToLoad.posx), tonumber(aBirdToLoad.posy), tonumber(aBirdToLoad.posz) )
				, vec3.new( tonumber(aBirdToLoad.dirx), tonumber(aBirdToLoad.diry), tonumber(aBirdToLoad.dirz) )
				, tonumber(aBirdToLoad.health)
				, tonumber(aBirdToLoad.age)
				)

			aBirdToLoad = birdCursor:fetch(aBirdToLoad, "a")
		end

		-- close the cursor
		birdCursor:close()

end

-- load snapshot data
function flocker.db.LoadSnapshot( snapshotNameString )

	-- create environment object
	local environment	= assert (luasql.sqlite3(), "LoadSnapshot: Failed to initialize sqlite")

	-- connect to data source
	local dbConnection	= assert (environment:connect("flocker.sqlite"), "LoadSnapshot: Failed to connect to database")

	if( snapshotNameString ) then
			-- grab a new cursor for the one specific row
		local snapshotRetrieverStmt	= string.format( "SELECT * FROM snapshot WHERE name LIKE '%s' ORDER BY id DESC", snapshotNameString)

		local snapshotCursor 		= assert( dbConnection:execute(snapshotRetrieverStmt) )
		local snapshot 				= snapshotCursor:fetch({},"a")
		snapshotCursor:close() -- clean up right away

		if snapshot then
			flocker.db.LoadDataForSnapshot( dbConnection, snapshot )
		end

	else -- ask the user what they want

		local result, snapshotIDTable = flocker.db.SelectSnapshots( dbConnection, false )
		-- a value of -1 means cancel
		if -1 < result then

			-- do the bird loading in here
			local chosenSnapshotID = snapshotIDTable[ result ]

			-- grab a new cursor for the one specific row
			local snapshotRetrieverStmt	= string.format( "SELECT * FROM snapshot WHERE id = %d", chosenSnapshotID)
			local snapshotCursor 		= assert( dbConnection:execute(snapshotRetrieverStmt) )
			local snapshot 				= snapshotCursor:fetch({},"a")
			snapshotCursor:close() -- clean up right away

			flocker.db.LoadDataForSnapshot( dbConnection, snapshot )

		end

	end

	-- clean up when we are done
	dbConnection:close()
	environment:close()

	flocker.WrapBirds()

end
